﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Runtime.Remoting.Messaging;
using MySql.Data.MySqlClient;
using OrionApplication.OrionLib.Orion;

namespace OrionApplication.OrionLib.Dao
{
    public class DaoOrionTable : DaoBase
    {
        public DaoOrionTable()
            : base()
        {
            this.TableName = "OrionTable";
            this.ViewName = "VOrionTable";
        }

        public OrionTable SelectById(Int32 OrionId)
        {
            lParam.Clear();
            OrionTable retour = new OrionTable();
            String req = this.DebutSelect 
                + this.TableName 
                + " WHERE OrionId=@orionid";

            lParam.Add(new MySqlParameter("@orionid", OrionId));

            MySqlDataReader dr = this.ExecuteQueryReader(req, lParam);

            if (dr.Read())
                retour = ChargeFromReader(dr);
            dr.Close();
            return retour;
        }

        public List<OrionTable> SelectAll()
        {
            lParam.Clear();
            List<OrionTable> retour = new List<OrionTable>();
            String req = this.DebutSelect
                + this.TableName;

            MySqlDataReader dr = this.ExecuteQueryReader(req);

            while (dr.Read())
                retour.Add(ChargeFromReader(dr));
            dr.Close();
            return retour;
        }

        public List<OrionTable> SelectByIdent(String IdentP, bool ActifP)
        {
            lParam.Clear();
            List<OrionTable> retour = new List<OrionTable>();
            String req = this.DebutSelect
                + this.TableName
                + " WHERE Ident=@ident AND OrionEtat=@orionetat";

            lParam.Add(new MySqlParameter("@ident", IdentP));
            lParam.Add(new MySqlParameter("@orionetat", ActifP));

            MySqlDataReader dr = this.ExecuteQueryReader(req, lParam);

            while (dr.Read())
                retour.Add(ChargeFromReader(dr));
            dr.Close();
            return retour;
        }

        public List<String> SelectDistinctIdent()
        {
            List<String> retour = new List<String>();

            String req = "SELECT DISTINCT Ident FROM " 
                + this.TableName;

            MySqlDataReader dr = this.ExecuteQueryReader(req);

            while (dr.Read())
                retour.Add(dr.GetString("Ident"));
            dr.Close();
            return retour;
        }

        public DataSet SelectByIdentDS(String IdentP, bool ActifP)
        {
            lParam.Clear();
            DataSet retour = new DataSet();
            String req = this.DebutSelect
                + this.TableName
                + " WHERE Ident=@ident AND OrionEtat=@orionetat";

            lParam.Add(new MySqlParameter("@ident", IdentP));
            lParam.Add(new MySqlParameter("@orionetat", ActifP));

            retour = this.ExecuteQueryDataSet(req, lParam);

            return retour;
        }

        public List<OrionTable> SelectByIdent(String IdentP)
        {
            lParam.Clear();
            List<OrionTable> retour = new List<OrionTable>();
            String req = this.DebutSelect
                + this.TableName
                + " WHERE Ident=@ident AND OrionEtat=@orionetat";

            lParam.Add(new MySqlParameter("@ident", IdentP));
            lParam.Add(new MySqlParameter("@orionetat", true));

            MySqlDataReader dr = this.ExecuteQueryReader(req, lParam);

            while (dr.Read())
                retour.Add(ChargeFromReader(dr));
            dr.Close();
            return retour;
        }

        public DataSet SelectByIdentDS(String IdentP)
        {
            lParam.Clear();
            DataSet retour = new DataSet();
            String req = this.DebutSelect
                + this.TableName
                + " WHERE Ident=@ident AND OrionEtat=@orionetat";

            lParam.Add(new MySqlParameter("@ident", IdentP));
            lParam.Add(new MySqlParameter("@orionetat", true));

            retour = this.ExecuteQueryDataSet(req, lParam);

            return retour;
        }

        public OrionTable Insert(OrionTable ot)
        {
            lParam.Clear();
            ot.OrionCrea = DateTime.Now;
            ot.OrionMaj = DateTime.Now;

            OrionTable retour = new OrionTable();
            String req = "INSERT INTO " + TableName + "(OrionId, Ident, Text1, Text2, Text3, Date1, Date2, Date3, Double1, Double2, Double3, OrionCrea, OrionMaj, OrionUserId, OrionEtat)  VALUES(NULL," +
                " @Ident, @Text1, @Text2, @Text3, @Date1, @Date2, @Date3, @Double1, @Double2, @Double3, @OrionCrea, @OrionMaj, @OrionUserId, @OrionEtat)";

            MySqlParameter param = new MySqlParameter();
            param.ParameterName = "@Ident";
            param.Value = ot.Ident;
            lParam.Add(param);
            param = new MySqlParameter();
            param.ParameterName = "@Text1";
            param.Value = ot.Text1;
            lParam.Add(param);
            param = new MySqlParameter();
            param.ParameterName = "@Text2";
            param.Value = ot.Text2;
            lParam.Add(param);
            param = new MySqlParameter();
            param.ParameterName = "@Text3";
            param.Value = ot.Text3;
            lParam.Add(param);
            param = new MySqlParameter();
            param.ParameterName = "@Date1";
            param.Value = ot.Date1;
            lParam.Add(param);
            param = new MySqlParameter();
            param.ParameterName = "@Date2";
            param.Value = ot.Date2;
            lParam.Add(param);
            param = new MySqlParameter();
            param.ParameterName = "@Date3";
            param.Value = ot.Date3;
            lParam.Add(param);
            param = new MySqlParameter();
            param.ParameterName = "@Double1";
            param.Value = ot.Double1;
            lParam.Add(param);
            param = new MySqlParameter();
            param.ParameterName = "@Double2";
            param.Value = ot.Double2;
            lParam.Add(param);
            param = new MySqlParameter();
            param.ParameterName = "@Double3";
            param.Value = ot.Double3;
            lParam.Add(param);
            param = new MySqlParameter();
            param.ParameterName = "@OrionCrea";
            param.Value = ot.OrionCrea;
            lParam.Add(param);
            param = new MySqlParameter();
            param.ParameterName = "@OrionMaj";
            param.Value = ot.OrionMaj;
            lParam.Add(param);
            param = new MySqlParameter();
            param.ParameterName = "@OrionUserId";
            param.Value = ((OrionUser)CallContext.GetData(OrionConfBase.OrionUserConnected)).OrionId;
            lParam.Add(param);
            param = new MySqlParameter();
            param.ParameterName = "@OrionEtat";
            param.Value = 1;
            lParam.Add(param);

            this.ExecuteNonQuery(req, lParam);
            retour = this.SelectById(Convert.ToInt32(this.LastInsertId));

            return retour;
        }

        public OrionTable Update(OrionTable ot)
        {
            lParam.Clear();
            ot.OrionMaj = DateTime.Now;

            OrionTable retour = new OrionTable();
            String req = "UPDATE " + TableName + " SET Ident=@Ident, Text1=@Text1, Text2=@Text2, Text3=@Text3, Date1=@Date1, Date2=@Date2, Date3=@Date3, Double1=@Double1, Double2=@Double2, Double3=@Double3, OrionMaj=@OrionMaj, OrionUserId=@OrionUserId " +
                " WHERE OrionId=@OrionId";

            MySqlParameter param = new MySqlParameter();
            param.ParameterName = "@Ident";
            param.Value = ot.Ident;
            lParam.Add(param);
            param = new MySqlParameter();
            param.ParameterName = "@OrionId";
            param.Value = ot.OrionId;
            lParam.Add(param);
            param = new MySqlParameter();
            param.ParameterName = "@Text1";
            param.Value = ot.Text1;
            lParam.Add(param);
            param = new MySqlParameter();
            param.ParameterName = "@Text2";
            param.Value = ot.Text2;
            lParam.Add(param);
            param = new MySqlParameter();
            param.ParameterName = "@Text3";
            param.Value = ot.Text3;
            lParam.Add(param);
            param = new MySqlParameter();
            param.ParameterName = "@Date1";
            param.Value = ot.Date1;
            lParam.Add(param);
            param = new MySqlParameter();
            param.ParameterName = "@Date2";
            param.Value = ot.Date2;
            lParam.Add(param);
            param = new MySqlParameter();
            param.ParameterName = "@Date3";
            param.Value = ot.Date3;
            lParam.Add(param);
            param = new MySqlParameter();
            param.ParameterName = "@Double1";
            param.Value = ot.Double1;
            lParam.Add(param);
            param = new MySqlParameter();
            param.ParameterName = "@Double2";
            param.Value = ot.Double2;
            lParam.Add(param);
            param = new MySqlParameter();
            param.ParameterName = "@Double3";
            param.Value = ot.Double3;
            lParam.Add(param);
            param = new MySqlParameter();
            param.ParameterName = "@OrionMaj";
            param.Value = ot.OrionMaj;
            lParam.Add(param);
            param = new MySqlParameter();
            param.ParameterName = "@OrionUserId";
            param.Value = ((OrionUser)CallContext.GetData(OrionConfBase.OrionUserConnected)).OrionId;
            lParam.Add(param);

            this.ExecuteNonQuery(req, lParam);
            retour = this.SelectById(ot.OrionId);

            return retour;
        }

        private OrionTable ChargeFromReader(MySqlDataReader dr)
        {
            OrionTable retour = new OrionTable();
            retour.OrionId = dr.GetInt32("OrionId");

            if (dr.IsDBNull(1))
                retour.Ident = string.Empty;
            else
                retour.Ident = dr.GetString("Ident");
            if (dr.IsDBNull(2))
                retour.Text1 = string.Empty;
            else
                retour.Text1 = dr.GetString("Text1");
            if (dr.IsDBNull(3))
                retour.Text2 = string.Empty;
            else
                retour.Text2 = dr.GetString("Text2");
            if (dr.IsDBNull(4))
                retour.Text3 = string.Empty;
            else
                retour.Text3 = dr.GetString("Text3");
            if (dr.IsDBNull(5))
                retour.Date1 = null;
            else
                retour.Date1 = dr.GetDateTime("Date1");
            if (dr.IsDBNull(6))
                retour.Date2 = null;
            else
                retour.Date2 = dr.GetDateTime("Date2");
            if (dr.IsDBNull(7))
                retour.Date3 = null;
            else
                retour.Date3 = dr.GetDateTime("Date3");
            if (dr.IsDBNull(8))
                retour.Double1 = 0.0;
            else
                retour.Double1 = dr.GetDouble("Double1");
            if (dr.IsDBNull(9))
                retour.Double2 = 0.0;
            else
                retour.Double2 = dr.GetDouble("Double2");
            if (dr.IsDBNull(10))
                retour.Double3 = 0.0;
            else
                retour.Double3 = dr.GetDouble("Double3");

            retour.OrionCrea = dr.GetDateTime("OrionCrea");
            retour.OrionMaj = dr.GetDateTime("OrionMaj");
            retour.OrionUserId = dr.GetInt32("OrionUserId");
            retour.OrionEtat = dr.GetBoolean("OrionEtat");

            return retour;
        }
    }
}
